import akshare as ak
import pandas as pd
from sqlalchemy import create_engine
import pymysql

# 连接mysql数据库
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3308/stock?charset=utf8')


# 获取同花顺资金流向
def getThsFundInfo():
    # 个股资金流向 symbol="即时"; choice of {“即时”, "3日排行", "5日排行", "10日排行", "20日排行"}
    stock_fund_flow_individual = ak.stock_fund_flow_individual()
    print(stock_fund_flow_individual)

# 获取东方财富资金流向
def getDfcfFundInfo():
    # 指定股票资金流
    stock_individual_fund_flow = ak.stock_individual_fund_flow(stock="300068", market="sz")
    print(stock_individual_fund_flow)
    print(stock_individual_fund_flow.loc[0]['日期'])
    # 按日期排序
    sorted_flow = stock_individual_fund_flow.sort_values(by='日期', ascending=False)
    print(sorted_flow)

# 获取股票历史每日行情
def getAllStockData() -> pd.DataFrame:
    # 调用实时行情接口，获取所有股票，然后再分别获取每只票的历史行情
    current_all_stock = ak.stock_zh_a_spot_em()
    # code -> name
    stock_code_map = {}
    # 遍历
    for index, row in current_all_stock.iterrows():
        stock_code_map[row['代码']] = row['名称']

    df_columns = [
        "code",
        "name",
        "trade_date",
        "open_price",
        "high_price",
        "low_price",
        "close_price",
        "change_amount",
        "change_percent",
        "amount",
        "turnover_rate"
    ]
    # 获取每只股票的历史行情
    # 先拿第一个，主要是拿到对应结构的DataFrame
    hist_df = pd.DataFrame(columns=df_columns)

    i = 1
    count = len(stock_code_map)
    for code, name in stock_code_map.items():
        # 跳过北京交易所的股票，代码8开头
        if str(code).startswith('8'):
            continue
        stock_zh_a_hist = ak.stock_zh_a_hist(symbol=code, period='daily', start_date='20220923', end_date='20220923',
                                             adjust='hfq')

        # 可能在指定时间段没有数据，比如今天刚上市的新股
        if stock_zh_a_hist.empty:
            continue

        current_df = pd.DataFrame(columns=df_columns)
        current_df["trade_date"] = stock_zh_a_hist["日期"]
        current_df['open_price'] = stock_zh_a_hist['开盘']
        current_df['high_price'] = stock_zh_a_hist['最高']
        current_df['low_price'] = stock_zh_a_hist['最低']
        current_df['close_price'] = stock_zh_a_hist['收盘']
        current_df['change_amount'] = stock_zh_a_hist['涨跌额']
        current_df['change_percent'] = stock_zh_a_hist['涨跌幅']
        current_df['amount'] = round(stock_zh_a_hist['成交额'] / 100000000, 2)
        current_df['turnover_rate'] = stock_zh_a_hist['换手率']
        # 这两行放在上面至少一行之后，不然无法赋值（我哪知道赋值多少行？）
        current_df['code'] = code
        current_df['name'] = name

        # 插入mysql表，stock_daily_hfq
        current_df.to_sql('stock_daily_hfq', engine, index=False, if_exists='append')

        # hist_df = pd.concat([hist_df, current_df], ignore_index=True, copy=False)
        print("%s  done......%d/%d" % (name, i, count))
        i = i + 1

    return hist_df


all_data = getAllStockData()
# getDfcfFundInfo()

